﻿using System;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace OpenXmlTest
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");
            System.Console.WriteLine("Create document");
            using (var spreadsheet = SpreadsheetDocument.Create("outputBold.xlsx", DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                System.Console.WriteLine("Creating workbook");
                spreadsheet.AddWorkbookPart();
                spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                System.Console.WriteLine("Creating sheet");

                var wsPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
                wsPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();
                System.Console.WriteLine("Creating style part");

                var stylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
                stylesPart.Stylesheet = new DocumentFormat.OpenXml.Spreadsheet.Stylesheet();

                System.Console.WriteLine("Add styles");
                stylesPart.Stylesheet.Fonts = new DocumentFormat.OpenXml.Spreadsheet.Fonts();
                stylesPart.Stylesheet.Fonts.Count = 2;
                stylesPart.Stylesheet.Fonts.Append(new Font());
                var headerFont = new Font();
                headerFont.Color = new Color() { Rgb = HexBinaryValue.FromString("FFFFFF"), };
                headerFont.Bold = new Bold();
                stylesPart.Stylesheet.Fonts.AppendChild(headerFont);


                System.Console.WriteLine("Create fills");
                stylesPart.Stylesheet.Fills = new Fills();
                var myBlue = new PatternFill() { PatternType = PatternValues.Solid };
                myBlue.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("0066CC") };
                myBlue.BackgroundColor = new BackgroundColor { Indexed = 64 };

                stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } }); // required, reserved by Excel
                stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } }); // required, reserved by Excel
                stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = myBlue });
                stylesPart.Stylesheet.Fills.Count = 3;

                // blank border list
                stylesPart.Stylesheet.Borders = new Borders();
                stylesPart.Stylesheet.Borders.Count = 1;
                stylesPart.Stylesheet.Borders.AppendChild(new Border());

                // blank cell format list
                stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
                stylesPart.Stylesheet.CellStyleFormats.Count = 1;
                stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());

                // cell format list
                stylesPart.Stylesheet.CellFormats = new CellFormats();
                // empty one for index 0, seems to be required
                stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());
                // cell format references style format 0, font 0, border 0, fill 2 and applies the fill
                stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 1, BorderId = 0, FillId = 2, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.General });
                stylesPart.Stylesheet.CellFormats.Count = 2;

                stylesPart.Stylesheet.Save();

                // columns
                var columns = new Columns();
                columns.Append(new Column
                {
                    Min = 1,
                    Max = 1,
                    CustomWidth = true,
                    Width = 25,
                });
                columns.Append(new Column
                {
                    Min = 2,
                    Max = 2,
                    CustomWidth = true,
                    Width = 25,
                });

                columns.Append(new Column
                {
                    Min = 3,
                    Max = 3,
                    CustomWidth = true,
                    Width = 25,
                });

                wsPart.Worksheet.Append(columns);

                System.Console.WriteLine("Creating data");

                var sheetData = wsPart.Worksheet.AppendChild(new SheetData());

                var row = sheetData.AppendChild(new Row());
                row.AppendChild(new Cell
                {
                    CellValue = new CellValue("This"),
                    DataType = CellValues.String,
                    StyleIndex = 1,

                });
                row.AppendChild(new Cell
                {
                    CellValue = new CellValue("Is"),
                    DataType = CellValues.String,
                    StyleIndex = 1
                });
                row.AppendChild(new Cell
                {
                    CellValue = new CellValue("Styled Header"),
                    DataType = CellValues.String,
                    StyleIndex = 1
                });

                row = sheetData.AppendChild(new Row());
                row.AppendChild(new Cell
                {
                    CellValue = new CellValue("This1"),
                    DataType = CellValues.String,
                });
                row.AppendChild(new Cell
                {
                    CellValue = new CellValue("Is1"),
                    DataType = CellValues.String,
                });
                row.AppendChild(new Cell
                {
                    CellValue = new CellValue("Styled Value1"),
                    DataType = CellValues.String,
                });

                row = sheetData.AppendChild(new Row());
                row.AppendChild(new Cell
                {
                    CellValue = new CellValue("This2"),
                    DataType = CellValues.String,
                });
                row.AppendChild(new Cell
                {
                    CellValue = new CellValue("Is2"),
                    DataType = CellValues.String,
                });
                row.AppendChild(new Cell
                {
                    CellValue = new CellValue("Styled Value2"),
                    DataType = CellValues.String,
                });

                System.Console.WriteLine("Saving worksheet");
                wsPart.Worksheet.Save();

                System.Console.WriteLine("Creating sheet list");
                var sheets = spreadsheet.WorkbookPart.Workbook.AppendChild(new Sheets());
                sheets.AppendChild(new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(wsPart), SheetId = 1, Name = "Test Sheet" });

                System.Console.WriteLine("Saving workbook");
                spreadsheet.WorkbookPart.Workbook.Save();

                System.Console.WriteLine("Done.");

            }
        }
    }
}
